﻿CREATE  TRIGGER [dbo].[trgU_dtFBK_calchrccrt] ON dbo.dtFBK FOR UPDATE
AS

IF UPDATE(CodeID)
BEGIN
  RAISERROR('Updates to Dep_id not allowed!', 16, 1)
  ROLLBACK TRAN
END
ELSE
IF UPDATE(mgrid) or UPDATE(hCode)
BEGIN
UPDATE    E
SET              E.hrclvl = E.hrclvl - I.hrclvl + 
							CASE WHEN I.mgrid IS NULL THEN 0 ELSE M.hrclvl + 1 END, 
			E.hrccrt = ISNULL(M.hrccrt, '.') + CAST(I.CodeID AS varchar(10)) + '.' + 
						ISNULL(RIGHT(E.hrccrt, LEN(E.hrccrt) - LEN(I.hrccrt)), ''), 
			E.hccrt = ISNULL(M.hccrt, '') + ISNULL(I.hCode, '') + '.' + 
						ISNULL(RIGHT(E.hccrt, LEN(E.hccrt) - LEN(I.hccrt)), '')
FROM         dtFBK AS E INNER JOIN
                      INSERTED AS I ON E.hrccrt LIKE I.hrccrt + '%' LEFT OUTER JOIN
                      dtFBK AS M ON I.mgrid = M.CodeID
END




/*
IF UPDATE(CodeID)
BEGIN
  RAISERROR('Updates to Dep_id not allowed!', 16, 1)
  ROLLBACK TRAN
END
ELSE
IF UPDATE(mgrid)
BEGIN
  UPDATE E
  SET hrclvl = E.hrclvl - I.hrclvl + CASE
                              WHEN I.mgrid IS NULL THEN 0
                              ELSE M.hrclvl + 1
                            END,
      hrccrt = ISNULL(M.hrccrt, '.') +
                  CAST(I.CodeID AS varchar(10)) + '.' +
                  isnull(right(E.hrccrt, len(E.hrccrt) - len(I.hrccrt)),''),
hccrt = ISNULL(M.hccrt, '.') +
                  isnull(I.hCode,'') + '.' +
                  isnull(right(E.hccrt, len(E.hccrt) - len(I.hccrt)),'')
  FROM dtFBK AS E JOIN INSERTED AS I
    ON E.hrccrt LIKE I.hrccrt + '%'
    LEFT OUTER JOIN dtFBK AS M
    ON I.mgrid = M.CodeID
END

IF UPDATE(hCode)
BEGIN
  UPDATE E
  SET hccrt = ISNULL(M.hccrt, '') +
                  isnull(I.hCode,'') + '.' +
                  isnull(right(E.hccrt, len(E.hccrt) - len(I.hccrt)),'')
  FROM dtFBK AS E JOIN INSERTED AS I
    ON E.hrccrt LIKE I.hrccrt + '%'
    LEFT OUTER JOIN dtFBK AS M
    ON I.mgrid = M.CodeID
END

*/


